Re: pgAgent and WAL streaming

--As of February 14, 2013 3:10:07 AM +0000, Keith Ouellette is alleged to
have said:

> The question I have is we are looking on using pgAgent to schedule
> running an SQL script periodically. How does that work in this scenario?
> I am assuming that the "production" server will just execute the script
> based on the configured schedule. But the standby user can not execute
> the same script as that would be duplicated. Does pgAgent know not to run
> when the database is in "archive" mode automatically? In the event of a
> "production" failure, the "archive" (now promoted to "production") would
> have to continue the schedule.
>
> Is there a way to accomplish what we are attempting to do?

--As for the rest, it is mine.

My solution would be to have the script write a timestamp someplace into
the database (probably in a table created just for it), and schedule the
'archive' script to run a minute or two after the production script should
complete.  If the script sees a recent timestamp, it quits without running.
(I would have the same version script on both boxes: The production one
wouldn't see the timestamp, so it would run, and if there's ever a case
where you need to switch the boxes around, you can do so with little fuss.)

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------